package com.llj.chatbidemo.config;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//新版数据库元数据收集：只需有查询权限就可手机数据库信息
@Service
public class MetadataCollector {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    String dataBaseName = "redonline";
    // 主入口方法
    public JSONObject generateMetadata() throws SQLException {
        JSONObject metadata = new JSONObject();
        metadata.put("version", "1.0.0");
        JSONArray tables = new JSONArray();

        // 获取所有表名
        List<String> tableNames = getTableNames();

        for (String tableName : tableNames) {
            JSONObject tableInfo = new JSONObject();
            tableInfo.put("physical_name", tableName);
            tableInfo.put("technical_comment", getTableComment(tableName));
            tableInfo.put("first_row_sample", getFirstRowSample(tableName));
            tables.add(tableInfo);
        }

        metadata.put("tables", tables);
        return metadata;
    }

    // 获取所有表名
    private List<String> getTableNames() throws SQLException {
        List<String> tables = new ArrayList<>();
        try (Connection conn = jdbcTemplate.getDataSource().getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getTables(dataBaseName, null, "%", new String[]{"TABLE"});
            while (rs.next()) {
                tables.add(rs.getString("TABLE_NAME"));
            }
        }
        return tables;
    }

    // 获取表注释
    private String getTableComment(String tableName) throws SQLException {
        try (Connection conn = jdbcTemplate.getDataSource().getConnection()) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getTables(dataBaseName, null, tableName, new String[]{"TABLE"});
            return rs.next() ? rs.getString("REMARKS") : "";
        }
    }

    // 获取首行数据样本
    private JSONObject getFirstRowSample(String tableName) {
        String sql = "SELECT * FROM " + tableName + " LIMIT 1";
        try {
            return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
                ResultSetMetaData meta = rs.getMetaData();
                JSONObject row = new JSONObject();
                for (int i = 1; i <= meta.getColumnCount(); i++) {
                    row.put(meta.getColumnName(i), rs.getObject(i));
                }
                return row;
            });
        } catch (EmptyResultDataAccessException e) {
            return new JSONObject(); // 空表返回空对象
        }
    }
}
